drop schema if exists riem;
create schema riem;
use riem;


create table department(
	dept_id char(2) primary key not null,
    dept_name varchar(100) not null,
    dean varchar(100)
);

-- create student table
create table students(
	uid char(4) primary key not null,
    uname varchar(100) not null,
    did char(2),
    foreign key (did) references department (dept_id)
);

create table course(
	cid char(4) primary key not null,
    cname varchar(100) not null
);

create table student_course(
	stu_id char(4),
    course_id char(4),
    credit integer,
    foreign key (stu_id) references students(uid),
    foreign key (course_id) references course(cid)
);



begin;
insert into department (dept_id, dept_name) values
	('01', 'Economics'),
    ('02', 'Finance and Accounting'),
    ('03', 'Managemnet');
insert into students values
	('1001', 'L.Zhang', '03'),
    ('1002', 'Heroine',  '02'),
    ('1003', 'X. Xu',  '03'),
	('1004', 'L. Liu',  '03'),
    ('1005', 'M. Chen',  '01'),
    ('1006', 'W. Xiao',  '01');
insert into course values
	('0001', '程序设计基础'), ('0002','数据库原理及应用'), ('0003','中级微观经济学');
insert into student_course values('1001','0002',80),('1001','0001','20');
commit;



